﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using QuanLyNhaSach.QuanLyNhaSachDTO;
namespace QuanLyNhaSach.QuanLyNhaSachDao
{
    class SachDao
    {
        public static DataTable GetSachAll()
        {
            string sql = "select * from Sach where Active = True";
            return Database.ExcuQuery(sql);
        }
        public static bool KTTen(string ten)
        {
            string sql = "select * from Sach ";
            DataTable dt = Database.ExcuQuery(sql);
            for (int i = 0; i < dt.Rows.Count; i++)
                if (dt.Rows[i]["TenSach"].ToString() == ten && dt.Rows[i]["Active"].ToString() == "false")
                {
                    return true;
                }
            return false;
        }
        public static bool KTTenTT(string ten)
        {
            string sql = "select * from Sach ";
            DataTable dt = Database.ExcuQuery(sql);
            for (int i = 0; i < dt.Rows.Count; i++)
                if (dt.Rows[i]["TenSach"].ToString() == ten && dt.Rows[i]["Active"].ToString() == "True")
                {
                    return true;
                }
            return false;
        }
        public static bool Insert(SachDTO tl)
        {
            string sql;
            bool active = true;
            if (KTTen(tl.TenSach) == true)
            {
                sql = "Update  Sach set Active =" + active + " where TenSach=" + tl.TenSach + "";
                Database.ExcuNonQuery(sql);
                return true;
            }
            if (KTTenTT(tl.TenSach) == false && KTTen(tl.TenSach) == false)
            {
                sql = "insert into Sach(TenSach,MaLoaiSach,SoLuongTon,TacGia,GiaBan,Active) values('" + tl.TenSach + "','" + tl.MaLoaiSach + "'," + tl.SoLuongTon + ",'" + tl.TacGia + "'," + tl.GiaBan + "," + active + ")";

                Database.ExcuNonQuery(sql);
                return true;
            }

            return false;

        }
        public static void Delete(SachDTO tl)
        {
            bool active = false;
            string sql = "Update  Sach set Active =" + active + " where TenSach='" + tl.TenSach + "'";
            Database.ExcuNonQuery(sql);
        }
        public static void Update(SachDTO s)
        {
            string sql = "update  Sach set TenSach ='" + s.TenSach + "', TacGia='" + s.TacGia+ "', MaLoaiSach='" + s.MaLoaiSach + "', GiaBan=" + s.GiaBan + ", SoLuongTon=" + s.SoLuongTon + " where MaSach = " + s.MaSach + "";
            
            Database.ExcuNonQuery(sql);
        }
        public static int GetDonGiaTheoMaHD(string ma)
        {
            int a, ma1;
            ma1 = Convert.ToInt32(ma);
            string sql = "select * from Sach where Active=True and MaSach=" + ma1 + "";
            DataTable dt = Database.ExcuQuery(sql);
            a = Convert.ToInt32(dt.Rows[0]["GiaBan"].ToString());
            return a;

        }
        ////////////////Tra cuu sach////////////////////////////
        public static DataTable GetSachTenTraCuu(string ten)
        {
            string sql = "select * from Sach where TenSach LIKE '%" + ten + "%' and Active=True ";
            return Database.ExcuQuery(sql);
        }
        public static DataTable GetSachTacGiaTraCuu(string ten)
        {
            string sql = "select * from Sach where TacGia LIKE '%" + ten + "%' and Active=True ";
            return Database.ExcuQuery(sql);
        }
        public static DataTable GetSachTheLoaiTraCuu(int ma)
        {
            ma = int.Parse(ma.ToString());
            string sql = "select * from Sach where MaLoaiSach = '" + ma + "' and Active=True ";
            return Database.ExcuQuery(sql);
        }
    }
}
